# 方案1: 使用 xlwings（需要安装Excel软件，Windows/Mac）
"""
pip install xlwings
"""
# def method1_xlwings(input_path,output_path,origin_path):
#     import xlwings as xw
#     import pandas as pd
    
#     # 读取替换字典
#     df = pd.read_csv(input_path)
#     replace_dict = {
#         f"{row['车辆名称']}-{row['悬架高度']}-{row['路面']}-{row['障碍物名称']}-{row['高低标签']}-{row['前后系统']}-{row['测试次数']}": row['检测距离段']
#         for _, row in df.iterrows()
#     }
    
#     # 使用xlwings处理
#     app = xw.App(visible=False)  # 后台运行
#     try:
#         wb = app.books.open(origin_path)
#         sheet = wb.sheets[0]  # 第一个sheet
        
#         # 获取使用区域
#         used_range = sheet.used_range
#         values = used_range.value
        
#         # 替换数据
#         if isinstance(values, list):
#             for i, row in enumerate(values):
#                 if isinstance(row, list):
#                     for j, cell_value in enumerate(row):
#                         if isinstance(cell_value, str) and cell_value in replace_dict:
#                             values[i][j] = replace_dict[cell_value]
#                 else:
#                     if isinstance(row, str) and row in replace_dict:
#                         values[i] = replace_dict[row]
        
#         # 写回数据
#         used_range.value = values
        
#         # 保存
#         wb.save(output_path)
#         wb.close()
        
#     finally:
#         app.quit()


def method1_xlwings(input_path,output_path,origin_path):
    import xlwings as xw
    import pandas as pd
    
    # 读取替换字典
    df = pd.read_csv(input_path)
    replace_dict = {
        f"{row['车辆名称']}-{row['悬架高度']}-{row['路面']}-{row['障碍物名称']}-{row['高低标签']}-{row['前后系统']}-{row['测试次数']}": row['检测距离段']
        for _, row in df.iterrows()
    }

    vehicle_name = df["车辆名称"].iloc[0]
    # print("zxc_test::::::::::::::::::::::::::::::::::::::::::",vehicle_name)
    
    # 使用xlwings处理
    app = xw.App(visible=False)  # 后台运行
    try:
        wb = app.books.open(origin_path)
        sheet = wb.sheets[0]  # 第一个sheet
        
        used_range = sheet.used_range
        values = used_range.value
        
        # 替换数据
        if isinstance(values, list):
            for i, row in enumerate(values):
                # print(f"i========={i},row==========={row}")
                if isinstance(row, list):
                    for j, cell_value in enumerate(row):
                        if isinstance(cell_value, str):
                            if cell_value in replace_dict:  
                                # 命中替换
                                values[i][j] = replace_dict[cell_value]
                            # elif cell_value.startswith(vehicle_name) and cell_value[1:].isdigit():
                            elif cell_value.startswith(vehicle_name + "-"):
                                # 如果是 T+数字 但不在字典里 → 清空
                                values[i][j] = " "
                                
                else:
                    if isinstance(row, str):
                        if row in replace_dict:
                            values[i] = replace_dict[row]
                        elif row.startswith("T") and row[1:].isdigit():
                            values[i] = ""
        
        # 写回
        used_range.value = values
        
        # 保存
        wb.save(output_path)
        wb.close()
    finally:
        app.quit()


# 方案2: 使用 xlrd + xlwt（适用于.xls格式）
"""
pip install xlrd xlwt xlutils
"""
def method2_xlrd_xlwt():
    import pandas as pd
    
    # 读取替换字典
    df = pd.read_csv("processed_segments.csv")
    replace_dict = {
        f"{row['障碍物名称']}-{row['方向']}": row['检测距离段']
        for _, row in df.iterrows()
    }
    
    # 如果是.xlsx文件，先转换为.xls
    df_excel = pd.read_excel("PDC_KPI.xlsx")
    df_excel.to_excel("temp_template.xls", index=False)
    
    from xlutils.copy import copy
    import xlrd
    
    # 读取.xls文件
    rb = xlrd.open_workbook("temp_template.xls")
    wb = copy(rb)
    sheet = wb.get_sheet(0)
    
    # 读取原数据进行替换
    original_sheet = rb.sheet_by_index(0)
    for row_idx in range(original_sheet.nrows):
        for col_idx in range(original_sheet.ncols):
            cell_value = original_sheet.cell_value(row_idx, col_idx)
            if isinstance(cell_value, str) and cell_value in replace_dict:
                sheet.write(row_idx, col_idx, replace_dict[cell_value])
    
    wb.save("v3_updated_excel.xls")
    print("✅ xlrd/xlwt方案成功！文件保存为 v3_updated_excel.xls")

# 方案3: 纯pandas方案（最稳定，但会丢失复杂格式）
def method3_pandas_only():
    import pandas as pd
    
    # 读取替换字典
    df = pd.read_csv("processed_segments.csv")
    replace_dict = {
        f"{row['障碍物名称']}-{row['方向']}": row['检测距离段']
        for _, row in df.iterrows()
    }
    
    # 读取Excel的所有工作表
    excel_data = pd.read_excel("PDC_KPI.xlsx", sheet_name=None, header=None)
    
    # 创建ExcelWriter对象
    with pd.ExcelWriter("v3_updated_excel.xlsx", engine='openpyxl') as writer:
        for sheet_name, df_sheet in excel_data.items():
            # 对整个DataFrame进行替换
            df_replaced = df_sheet.replace(replace_dict)
            df_replaced.to_excel(writer, sheet_name=sheet_name, 
                               index=False, header=False)
    
    print("✅ 纯pandas方案成功！")

# 推荐使用顺序：
# 1. 先试修复后的openpyxl方案（上面的代码）
# 2. 如果有Excel软件，试xlwings方案
# 3. 最后使用纯pandas方案

def swap_to_excel(input_path,output_path,Excel_path):
    # log处理输出csv路径
    csv_path = input_path

    try:
        method1_xlwings(csv_path,output_path,Excel_path)
    except ImportError as e:
        print(f"缺少必要的库: {e}")

# if __name__ == "__main__":
#    # log处理输出csv路径
#     csv_path = r"D:\Project\tool_process_pdc\processed_segments.csv"

#     try:
#         method1_xlwings(csv_path)
#     except ImportError as e:
#         print(f"缺少必要的库: {e}")
      